package database;

import model.Course;
import model.Session;
import model.User;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.concurrent.ArrayBlockingQueue;

public class DBHandler{
	private static final int MAX_CONS = 50;
	private static DBHandler instance = null;
	private ArrayBlockingQueue<Connection> conPool;
	private DBHandler() throws ClassNotFoundException, SQLException, InterruptedException{
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "mysql";
		String userName = "root";
		String password = "root";
		Class.forName("com.mysql.jdbc.Driver"); 
		
		conPool = new ArrayBlockingQueue<Connection>(MAX_CONS);
		for (int i = 0; i < MAX_CONS; i++){
			Connection c = DriverManager.getConnection (url+dbName, userName, password);
			c.setAutoCommit(false);
			conPool.put(c);
		}
		
		Connection con = DriverManager.getConnection (url+dbName, userName, password);
		Statement stmt = null;
		ResultSet rs = null;
		try { 
			stmt = con.createStatement();
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS users(" +
								"userName VARCHAR(256) PRIMARY KEY," +
								"password VARCHAR(19)," +
								"name VARCHAR(19)," +
								"family	VARCHAR(19)," +
								"phone VARCHAR(16)," +
								"isAdmin BOOLEAN," +
								"courses BLOB" +
								") ENGINE = INNODB;");
			
			stmt = con.createStatement(); 
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS courses(" +
								"courseName VARCHAR(256) PRIMARY KEY," +
								"description VARCHAR(512)," +
								"numOfpts INT UNSIGNED," +
								"capacity INT UNSIGNED," +
								"numOfRegistered INT UNSIGNED," +
								"groupNum INT UNSIGNED," +								
								"sessions BLOB," +
								"creator VARCHAR(512), " +
								"users BLOB " +
								") ENGINE = INNODB;");
			
			stmt = con.createStatement();
			stmt.executeUpdate("CREATE TABLE IF NOT EXISTS stylesheets(" +
								"userName VARCHAR(256)," +
								"style VARCHAR(256)," +
								"stylesheet LONGTEXT," +
								"CONSTRAINT pk_s PRIMARY KEY (userName,style)"+
								") ENGINE = INNODB;");
		}
		finally { // Close the connection. Notice the order.
			try{
				if (rs != null)	rs.close();
				if (stmt != null)stmt.close();
				if (con != null) con.close();
			}
			catch(Exception e){
				e.printStackTrace();
			}
		}
	}
	 
	public static Object read(ResultSet rs, String column) throws SQLException,
			IOException, ClassNotFoundException {
		byte[] buf = rs.getBytes(column);
		if (buf != null) {
			ObjectInputStream objectIn = new ObjectInputStream(
					new ByteArrayInputStream(buf));
			return objectIn.readObject();
		}
		return null;
	}
	
	public Connection getLocalCon(){
		synchronized (conPool) {
			while (conPool.isEmpty()) {
				try {
					conPool.wait();
				} catch (InterruptedException ignored) {
				}
			}
			return conPool.remove();
		}
	}
	
	public void returnLocalCon(Connection con){
		synchronized (conPool) {
			try {
				conPool.add(con);
				conPool.notify();
			} catch (IllegalStateException e) {
			}
		}
	}
	
	public static DBHandler getHandler(){
		if (instance == null)
			try {
				instance = new DBHandler();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e){
				e.printStackTrace();
			}
		return instance;
	}
	
	public boolean addUser(User user,Connection con) throws UserExistsException{
		ResultSet rs = null;
		PreparedStatement stmt;

		try {
			stmt = con.prepareStatement("SELECT * FROM users WHERE username=?");
			stmt.setString(1, user.getUserName());
			
			rs = stmt.executeQuery();
			if (rs.next()){
				throw new UserExistsException();
			}
			
			stmt = con.prepareStatement("INSERT INTO users VALUES(?,?,?,?,?,?,?)");
			stmt.setString(1, user.getUserName());
			stmt.setString(2, user.getPassword());
			stmt.setString(3, user.getName());
			stmt.setString(4, user.getFamily());
			stmt.setString(5, user.getPhone());
			stmt.setBoolean(6, user.isAdmin());
			stmt.setObject(7, user.getCourses());//remove this if change
			stmt.executeUpdate();
		}	
		catch (SQLException e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			return false;
		}
		return true;
	}
	
	public User getUser(String userName,Connection con) throws IOException, ClassNotFoundException{
		ResultSet rs = null;
		PreparedStatement stmt;

		try {
			stmt = con.prepareStatement("SELECT * FROM users WHERE username=?");
			stmt.setString(1, userName);
			rs = stmt.executeQuery();
			if (!rs.next())
				return new User("", "", "", "", "", false,new ArrayList<String>());
			return new User(rs.getString("userName"), rs.getString("password"), rs.getString("name"), 
					rs.getString("family"), rs.getString("phone"), rs.getBoolean("isAdmin"),(ArrayList<String>) read(rs,"courses"));//remove this if change
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;//Happy, compiler? 
	}
	
	public ArrayList<User> getAllUsers(Connection con) throws IOException, ClassNotFoundException{
		ResultSet rs = null;
		PreparedStatement stmt;
		ArrayList<User> result = new ArrayList<User>();

		try {
			stmt = con.prepareStatement("SELECT * FROM users");
			rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new User(rs.getString("userName"),rs.getString("password"),
						rs.getString("name"),rs.getString("family"), rs.getString("phone"),
						rs.getBoolean("isAdmin"),(ArrayList<String>)read(rs,"courses"));
				result.add(user);
		
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return result;
		}	
	}
	
	public boolean addCourse(Course course,Connection con) throws CourseExistsException{
		ResultSet rs = null;
		PreparedStatement stmt;
		
		try {
			stmt = con.prepareStatement("SELECT * FROM courses WHERE courseName=?");
			stmt.setString(1, course.getCourseName());
			
			rs = stmt.executeQuery();
			if (rs.next()){
				throw new CourseExistsException();
			}
			
			stmt = con.prepareStatement("INSERT INTO courses VALUES(?,?,?,?,?,?,?,?,?)");
			stmt.setString(1, course.getCourseName());
			stmt.setString(2, course.getDescription());
			stmt.setInt(3, course.numOfpts());
			stmt.setInt(4, course.getCapacity());
			stmt.setInt(5, course.getNumOfRegistered());
			stmt.setInt(6, course.getGroupNum());
			stmt.setObject(7, course.getSessions());
			stmt.setString(8, course.getCreator());
			stmt.setObject(9,course.getUsers());
			stmt.executeUpdate();
		}	
		catch (SQLException e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			return false;
		}
		
		return true;
	}

	public Course getCourse(String courseName,Connection con) throws IOException, ClassNotFoundException{
		ResultSet rs = null;
		PreparedStatement stmt;

		try {
			stmt = con.prepareStatement("SELECT * FROM courses WHERE courseName=?");
			stmt.setString(1, courseName);
			rs = stmt.executeQuery();
			if (!rs.next()) 
				return new Course(" ", " ", 0, 0, 0, 0, new ArrayList<Session>(),"",new ArrayList<String>());
			
			return new Course(rs.getString("courseName"), rs.getString("description"), rs.getInt("numOfpts"), 
					rs.getInt("capacity"), rs.getInt("numOfRegistered"), rs.getInt("groupNum"),
					(ArrayList<Session>) read(rs,"sessions"),rs.getString("creator"),(ArrayList<String>)read(rs,"users"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}

	public ArrayList<Course> getAllCourses(Connection con) throws IOException, ClassNotFoundException{
		ResultSet rs = null;
		PreparedStatement stmt;
		ArrayList<Course> result = new ArrayList<Course>();
		
		try {
			stmt = con.prepareStatement("SELECT * FROM courses");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Course course = new Course(rs.getString("courseName"),rs.getString("description"),
						rs.getInt("numOfpts"),rs.getInt("capacity"), rs.getInt("numOfRegistered"),
						rs.getInt("groupNum"),(ArrayList<Session>)read(rs,"sessions"),rs.getString("creator"),(ArrayList<String>)read(rs,"users"));
				result.add(course);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return result;
		}
		
	}
	
	public ArrayList<Course> getCoursesByGroup(int groupNum,Connection con) throws IOException, ClassNotFoundException{
		ResultSet rs = null;
		PreparedStatement stmt;
		ArrayList<Course> result = new ArrayList<Course>();

		try {
			stmt = con.prepareStatement("SELECT * FROM courses WHERE groupNum=? ");
			stmt.setInt(1, groupNum);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Course course = new Course(rs.getString("courseName"),rs.getString("description"),
						rs.getInt("numOfpts"),rs.getInt("capacity"), rs.getInt("numOfRegistered"),
						rs.getInt("groupNum"),(ArrayList<Session>)read(rs,"sessions"),rs.getString("creator"),(ArrayList<String>)read(rs,"users"));
				result.add(course);
		
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return result;
		}
		
	}

	public ArrayList<Course> getCoursesByUserName(String userName,Connection con) 
		throws IOException, ClassNotFoundException, SQLException{
		ResultSet rs = null;
		PreparedStatement stmt = null;
		ArrayList<Course> result = new ArrayList<Course>();
		
		User user = getUser(userName,con);
		if (user.getCourses().size() == 0)
			return new ArrayList<Course>();
		ArrayList<String> currentCourses = user.getCourses();
		String query = new String("SELECT * FROM courses WHERE courseName IN (");
		query += "?";
		for(int i = 1; i < currentCourses.size(); i++){
			query += ",?";
		}
		query += ")";
		stmt = con.prepareStatement(query);
		try{
			for(int i = 1; i <= currentCourses.size(); i++){
				stmt.setString(i, currentCourses.get(i-1));
			}
			
			rs = stmt.executeQuery();
			while (rs.next()) {
				Course course = new Course(rs.getString("courseName"),rs.getString("description"),
						rs.getInt("numOfpts"),rs.getInt("capacity"), rs.getInt("numOfRegistered"),
						rs.getInt("groupNum"),(ArrayList<Session>)read(rs,"sessions"),rs.getString("creator"),(ArrayList<String>)read(rs,"users"));
				result.add(course);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return result;
		}
	}
	
	public void unRegisterUserFromCourse(String userName,String courseName,Connection con) 
		throws IOException, ClassNotFoundException, SQLException{
		Course course = getCourse(courseName,con);
		User user = getUser(userName,con);
		PreparedStatement stmt = null;
		ArrayList<String> courseUsers = new ArrayList<String>(course.getUsers());
		courseUsers.remove(userName);
		
		stmt = con.prepareStatement("UPDATE courses " +
				"SET numOfRegistered=?, users=? " +
				"WHERE courseName=? ");
		stmt.setInt(1, course.getNumOfRegistered()-1);
		stmt.setObject(2, courseUsers);
		stmt.setString(3, courseName);
		stmt.executeUpdate();
		
		ArrayList<String> courses = new ArrayList<String>(user.getCourses());
		courses.remove(courseName);
		stmt = con.prepareStatement("UPDATE users " +
				"SET courses=? " +
				"WHERE userName=? ");
		stmt.setObject(1, courses);
		stmt.setString(2, userName);
		stmt.executeUpdate();
	}

	public void registerUserToCourse(String userName, String courseName,Connection con)
			throws IOException, ClassNotFoundException, SQLException {
		Course course = getCourse(courseName,con);
		User user = getUser(userName,con);
		PreparedStatement stmt = null;
		ArrayList<String> courseUsers =  new ArrayList<String>(course.getUsers());
		courseUsers.add(userName);

		stmt = con.prepareStatement("UPDATE courses SET numOfRegistered=?, users=? WHERE courseName=? ");
		stmt.setInt(1, course.getNumOfRegistered() + 1);
		stmt.setObject(2, courseUsers);
		stmt.setString(3, courseName);
		stmt.executeUpdate();
		

		ArrayList<String> userCourses = new ArrayList<String>(user.getCourses());
		userCourses.add(courseName);
		stmt = con.prepareStatement("UPDATE users " + "SET courses=? "
				+ "WHERE userName=? ");
		stmt.setObject(1, userCourses);
		stmt.setString(2, userName);
		stmt.executeUpdate();
	}

	public void removeCourse(String courseName,Connection con) throws SQLException {
		PreparedStatement stmt = con.prepareStatement("DELETE FROM courses WHERE courseName=? ");
		stmt.setString(1, courseName);
		stmt.executeUpdate();
		
	}

	public void removeUser(String userName,Connection con) throws SQLException {
		PreparedStatement stmt = con.prepareStatement("DELETE FROM users WHERE userName=? ");
		stmt.setString(1, userName);
		stmt.executeUpdate();
		
		stmt = con.prepareStatement("DELETE FROM stylesheets WHERE userName=? ");
		stmt.setString(1, userName);
		stmt.executeUpdate();
	}

	public void updateUser(String userName, String password, String name,
		String family, String phone, Connection con) throws SQLException, IOException, ClassNotFoundException {
		User user = getUser(userName,con);

		PreparedStatement stmt = con.prepareStatement("UPDATE users " +
				"SET password=?, name=?, family=?, phone=?" +
				"WHERE userName=? ");
	
		stmt.setString(1, password.equals("")?user.getPassword():password);
		stmt.setString(2, name.equals("")?user.getName():name);
		stmt.setString(3, family.equals("")?user.getFamily():family);
		stmt.setString(4, phone.equals("")?user.getPhone():phone);
		stmt.setString(5, userName);
		stmt.executeUpdate();

	}

	public ByteArrayInputStream getUserStyle(String userName, String style,Connection con) throws SQLException, IOException, ClassNotFoundException {
		ResultSet rs = null;
		PreparedStatement stmt = con.prepareStatement("SELECT * FROM stylesheets " +
				"WHERE userName=? AND style=?");
		stmt.setString(1, userName);
		stmt.setString(2, style);
		rs = stmt.executeQuery();
		if (rs.next()){
			return new ByteArrayInputStream(rs.getString("stylesheet").getBytes());
		}
		return new ByteArrayInputStream("".getBytes());
	}
	
	public ArrayList<String> getStyleSheetNames(String userName,Connection con) throws SQLException{
		ResultSet rs = null;
		ArrayList<String> result = new ArrayList<String>();
		PreparedStatement stmt = con.prepareStatement("SELECT style FROM stylesheets " +
				"WHERE userName=?");
		stmt.setString(1,userName);
		rs = stmt.executeQuery();
		while(rs.next()){
			result.add(rs.getString("style"));
		}
		return result;
	}

	public void addStylesheet(String userName, String fileName, byte[] bs,Connection con) throws SQLException {
		String nbs = new String(bs);
		PreparedStatement stmt = con.prepareStatement("INSERT INTO stylesheets VALUES(?,?,?) ");
		stmt.setString(1, userName);
		stmt.setString(2, fileName);
		stmt.setString(3, nbs);
		stmt.executeUpdate();
	}

	public ArrayList<Course> makeSearch(String courseName, String description,Connection con) throws SQLException, IOException, ClassNotFoundException{
		ResultSet rs = null;
		ArrayList<Course> result = new ArrayList<Course>();
		PreparedStatement stmt = con.prepareStatement("SELECT * " +
						"FROM courses " +
						"WHERE courseName LIKE ? AND description LIKE ?");
		stmt.setString(1,"%"+courseName+"%");
		stmt.setString(2,"%"+description+"%");
		rs = stmt.executeQuery();
		while(rs.next()){
			result.add(new Course(rs.getString("courseName"), rs.getString("description"), rs.getInt("numOfpts"), 
					rs.getInt("capacity"), rs.getInt("numOfRegistered"), rs.getInt("groupNum"),
					(ArrayList<Session>) read(rs,"sessions"),rs.getString("creator"),(ArrayList<String>)read(rs,"users")));
		}
		return result;
	}
} 
